﻿create proc sp_MergeProgram
AS
MERGE Program AS Target
USING 
(
	SELECT DISTINCT 
		ProgramId
		, ProgramName
		, ProgramInvestmentAlignmentCategory
		, KeyBusinessContact
		, TechnologyProgramManager
		, TechProgramManagerSMT
		, TechProgramManagerSTLT
	FROM ProjectProgramTemp
) AS Source
ON
(
	Target.Id = Source.ProgramId 
)
WHEN MATCHED THEN
	UPDATE SET 
		ProgramName = Source.ProgramName
		, ProgramInvestmentAlignmentCategory = Source.ProgramInvestmentAlignmentCategory
		, KeyBusinessContact = Source.KeyBusinessContact
		, TechnologyProgramManager = Source.TechnologyProgramManager
		, TechProgramManagerSMT = Source.TechProgramManagerSMT
		, TechProgramManagerSTLT = Source.TechProgramManagerSTLT
WHEN NOT MATCHED BY Target THEN
	INSERT 
	(
		Id
		, ProgramName
		, ProgramInvestmentAlignmentCategory
		, KeyBusinessContact
		, TechnologyProgramManager
		, TechProgramManagerSMT
		, TechProgramManagerSTLT
	) 
	Values
	(
		Source.ProgramId
		, Source.ProgramName
		, Source.ProgramInvestmentAlignmentCategory
		, Source.KeyBusinessContact
		, Source.TechnologyProgramManager
		, Source.TechProgramManagerSMT
		, Source.TechProgramManagerSTLT
	);


	select * from Program